﻿/******************************************************   
创建者: ljj
创建时间: 2018-01-17
修改:
修改时间: 
功能: 日均销设置
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using com.nm.library.Helper;
using george.Helper;
using george.Web.Helper;
using NHibernateVertica;
using InventoryToPredictAPI.model;

namespace InventoryToPredictAPI
{
    [Description("日均销设置")]
    public class GoodsSingleValidCalcAfterInfo05DHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：ljj
        /// Create Date：2018-01-17
        [MustLogin]
        [Description("日均销设置信息")]
        [ReturnType(typeof(IList<IGrouping<string, GoodsSingleValidCalcAfterInfo05DModel.ShowModel>>))]
        public ApiResult GetGoodsSingleValidCalcAfterInfo05DList([ParamDescription("店铺级别")] string ShopLevel,
            [ParamDescription("大类")] string KindID = "", [ParamDescription("性别")] string SexID = "")
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                if (string.IsNullOrEmpty(companyID))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(extrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var kindIDStr = KindID.GetString();
                var sql = $@"SELECT  kti.KindID ,
        kind.KindName ,
        kti.SexID ,
        sex.SexName ,
        kti.ItemOrSub ,
        kti.ShopLevel ,
        ISNULL(aftd.DaySaleQty, 0) AS DaySaleQty ,
        ISNULL(aftd.ChangeFlag, 0) AS ChangeFlag
FROM    ( SELECT    * ,
                    '{ShopLevel}' AS ShopLevel
          FROM      ndw.ndw_KindToItem
          WHERE     ExtrSource = '{extrSource}'
                    AND CompanyID = '{companyID}'
                    {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID IN {kindIDStr}")}
                    {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
        ) kti
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_Single_Valid_Calc_AfterInfo_05_D
                    WHERE   InfoName = '{ShopLevel}'
                            {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID IN {kindIDStr}")}
                  ) aftd ON kti.ShopLevel = aftd.InfoName
                            AND kti.KindID = aftd.KindID
                            AND kti.SexID = aftd.SexID
                            AND kti.ItemOrSub = aftd.ItemOrSub
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Kind
                     WHERE  ExtrSource = '{extrSource}'
                            {(string.IsNullOrEmpty(KindID) ? "" : $"AND KindID IN {kindIDStr}")}
                   ) kind ON kti.ExtrSource = kind.ExtrSource
                             AND kti.KindID = kind.KindID
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Sex
                     WHERE  ExtrSource = '{extrSource}'
                            {(string.IsNullOrEmpty(SexID) ? "" : $"AND SexID = '{SexID}'")}
                   ) sex ON kti.ExtrSource = sex.ExtrSource
                            AND kti.SexID = sex.SexID
ORDER BY kti.KindID ,
         kti.SexID ,
         kti.ItemOrSub";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<GoodsSingleValidCalcAfterInfo05DModel.ShowModel>.ConvertToModel(dt);
                var group = list.GroupBy(d => d.ShopLevel + "_" + d.KindID + "_" + d.SexID).ToList();
                return Success(group);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败!");
            }
        }

        /// Writer：ljj
        /// Create Date：2018-01-17
        [MustLogin]
        [JsonType]
        [Description("修改日均销")]
        [ReturnType(typeof(int),"0-成功")]
        public ApiResult ModifyGoodsSingleValidCalcAfterInfo05D([ParamDescription("关键字")] GoodsSingleValidCalcAfterInfo05DModel.ModifyModel param)
        {
            var tempTableName = $"ngoods.ngoods_{Guid.NewGuid():N}";
            var sourceTableName = "ngoods.ngoods_Single_Valid_Calc_AfterInfo_05_D";
            try
            {
                if (param == null) return Error("参数对象为空");
                if (param.modifyList == null) return Error("参数对象为空!");
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                if (string.IsNullOrEmpty(companyID))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(extrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var list = new List<GoodsSingleValidCalcAfterInfo05DModel.CreateModel>();
                foreach (var item in param.modifyList)
                {
                    list.AddRange(item.list.Select(d => new GoodsSingleValidCalcAfterInfo05DModel.CreateModel
                    {
                        InfoName = item.ShopLevel,
                        KindID = item.KindID,
                        SexID = item.SexID,
                        ItemOrSub = d.ItemOrSub,
                        DaySaleQty = d.DaySaleQty,
                    }));
                }
                var dt = DataTableHelper.LinqToDataTable(list);
                Helper.DropTempTable(tempTableName);
                Helper.CreateTempTable(tempTableName, sourceTableName);
                Helper.BulkCopy(dt, tempTableName);
                var newDT = JudgeIsModify(tempTableName);
                MergeGoodsSingleValidCalcAfterInfo05D(tempTableName, sourceTableName);
                if (newDT != null && newDT.Rows.Count > 0)
                {
                    MergeGoodsSingleValidCalcAfterInfo05D(newDT, sourceTableName);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("保存失败!");
            }
            finally
            {
                Helper.DropTempTable(tempTableName);
            }
        }

        /// <summary>
        /// 判断是否更改
        /// </summary>
        /// <param name="TempTableName"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2018-01-17
        public DataTable JudgeIsModify(string TempTableName)
        {
            try
            {
                var sql = $@"SELECT  temp.*
FROM    {TempTableName} temp
        INNER JOIN ngoods.ngoods_Single_Valid_Calc_AfterInfo_05_D aftd ON temp.InfoName = aftd.InfoName
                                                              AND temp.KindID = aftd.KindID
                                                              AND temp.SexID = aftd.SexID
                                                              AND temp.ItemOrSub = aftd.ItemOrSub
                                                              AND temp.DaySaleQty <> aftd.DaySaleQty";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return dt;
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

        /// <summary>
        /// merge
        /// </summary>
        /// <param name="TempTableName"></param>
        /// <param name="SourceTableName"></param>
        /// Writer：ljj
        /// Create Date：2018-01-17
        public void MergeGoodsSingleValidCalcAfterInfo05D(string TempTableName,string SourceTableName)
        {
            try
            {
                const string onConditions = @"targetTable.InfoName=sourceTable.InfoName
    and targetTable.KindID=sourceTable.KindID
	and targetTable.SexID=sourceTable.SexID
	and targetTable.ItemOrSub=sourceTable.ItemOrSub";
                var setConditions = $@"DaySaleQty=sourceTable.DaySaleQty";
                const string insertConditions =
                    @"InfoName,KindID,SexID,ItemOrSub,DaySaleQty,ChangeFlag";
                const string valuesConditions =
                    @"sourceTable.InfoName,sourceTable.KindID,sourceTable.SexID,sourceTable.ItemOrSub,sourceTable.DaySaleQty,1";
                Helper.MergeIntoTable(TempTableName, SourceTableName, onConditions, setConditions,
                    insertConditions, valuesConditions, true);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// merge
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="SourceTableName"></param>
        /// Writer：ljj
        /// Create Date：2018-01-17
        public void MergeGoodsSingleValidCalcAfterInfo05D(DataTable dt,string SourceTableName)
        {
            try
            {
                var tempTableName = $"ndw.temp_{Guid.NewGuid():N}";
                const string onConditions = @"targetTable.InfoName=sourceTable.InfoName
    and targetTable.KindID=sourceTable.KindID
	and targetTable.SexID=sourceTable.SexID
	and targetTable.ItemOrSub=sourceTable.ItemOrSub";
                var setConditions = $@"DaySaleQty=sourceTable.DaySaleQty ,
    ChangeFlag=1";
                const string insertConditions = @"";
                const string valuesConditions = @"";
                Helper.Merge(tempTableName, SourceTableName, dt, onConditions, setConditions,
                    insertConditions, valuesConditions, false);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：ljj
        /// Create Date：2018-01-17
        [MustLogin]
        [JsonType]
        [Description("复制日均销")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult CopyGoodsSingleValidCalcAfterInfo05D([ParamDescription("关键字")] GoodsSingleValidCalcAfterInfo05DModel.CopyModel param)
        {
            try
            {
                const string sql = @"DELETE  FROM ngoods.ngoods_Single_Valid_Calc_AfterInfo_05_D
WHERE   InfoName = '{0}';
INSERT  INTO ngoods.ngoods_Single_Valid_Calc_AfterInfo_05_D
        ( InfoName ,
          KindID ,
          ItemOrsub ,
          SexID ,
          DaySaleQty ,
          {2}
        )
        SELECT  InfoName ,
                KindID ,
                ItemOrsub ,
                SexID ,
                DaySaleQty ,
                ChangeFlag
        FROM    ngoods.ngoods_Single_Valid_Calc_AfterInfo_05_D
        WHERE   InfoName = '{1}';";
                if (param == null) return Error("参数对象为空");
                if (param.list == null) return Error("参数对象为空!");
                if (param.list.Any(d => d.TargeShopLevel == param.SourceShopLeve)) return Error("目标店铺级别不能跟数据源店铺级别相同!");
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                if (string.IsNullOrEmpty(companyID))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(extrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sb = new StringBuilder();
                foreach (var item in param.list)
                {
                    sb.AppendLine(string.Format(sql, item.TargeShopLevel, param.SourceShopLeve,
                        ((param.DaySaleQty == null || param.DaySaleQty == 0) ? "ChangeFlag" : "1")));
                }
                var cmd = Helper.GetSqlStringCommand(sb.ToString());
                Helper.ExecuteNonQuery(cmd);
                return Success(0);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("复制失败!");
            }
        }

        /// Writer：ljj
        /// Create Date：2018-01-18
        [MustLogin]
        [Description("计算日均销")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult CalGoodsSingleValidCalcAfterInfo05D()
        {
            try
            {
                var dateTime = DateTime.Now.ToString("yyyy-MM-dd");
                var server = IpAddressHelper.WebConfig("python-server"); //"192.168.10.127";
                var username = IpAddressHelper.WebConfig("python-server-user"); //"bi";
                var port = Convert.ToInt32(IpAddressHelper.WebConfig("python-server-port")); //"22";
                var password = IpAddressHelper.WebConfig("python-server-pass"); //"Km_etl2017";
                var cmdJudge = $"python /home/bi/scripts/python/etl/ndw/SP_ngoods_Single_Valid_Calc_AfterInfo_05_D_Monitor.py";
                var cmd = $"python /home/bi/scripts/python/etl/ndw/SP_ngoods_Single_Valid_Calc_AfterInfo_05_D.py  {dateTime}";
                var resJudge = ConnLinux(server, username, port, password, cmdJudge);
                if (resJudge.Contains("1")) return Error("当前有用户正在计算!");
                var result = ConnLinux(server, username, port, password, cmd);
                return result.Contains("success") ? Success(0) : Error(result);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("计算失败!");
            }
        }
    }
}
